
-- **************************************************************************
--    Project Name:   服务质量-客服日报-月度数据剔除
--    Job Name:       jms_dm__dm_mng_complaint_daliy_mth_exclude_mt
--    Author :        季修魁
--    date：          2023/11/22
--    remark: 每月2号跑上月数据整月的数据
-- **************************************************************************
-- **************************************************************************

---1.逻辑剔除明细 dm_mng_complaint_daliy_waybill_detail_dt、

--- exclude_metric_item1	剔除指标项1状态（1小时响应,,0-否，1-是）
--- exclude_metric_item2	剔除指标项2状态（24小时解决,0-否，1-是）
--- exclude_metric_item3	剔除指标项3状态（二次投诉,0-否，1-是）

insert overwrite table jms_dm.dm_mng_complaint_daliy_waybill_detail_dt  partition (dt)
select a.work_type
       ,a.work_order_no
       ,a.waybill_no
       ,a.accept_network_code
       ,a.accept_network_name
       ,a.duty_network_code
       ,a.duty_network_name
       ,a.transfer_network_code
       ,a.transfer_network_name
       ,a.work_order_status
       ,a.response_state
       ,a.total_duration
       ,a.work_order_source_id
       ,a.create_time
       ,a.sys_time
       ,a.update_time
       ,a.last_close_time
       ,a.source_code
       ,a.transfer_network_type_id
       ,a.return_time
       ,a.send_back_code
       ,a.work_order_channel
       ,a.auto_finish
       ,a.accept_proxy_code
       ,a.accept_proxy_name
       ,a.accept_network_type
       ,a.duty_proxy_code
       ,a.duty_proxy_name
       ,a.duty_network_type
       ,a.transfer_proxy_code
       ,a.transfer_proxy_name
       ,a.transfer_network_type
       ,a.date_time
       ,a.t_1_falg
       ,a.t_2_falg
       ,a.t_3_falg
       ,a.t_4_falg
       ,a.put2_ts_flag
       ,a.pingt2_ts_flag
       ,a.tichu_all_24_flag
       ,a.tichu_pingt_24_flag
       ,a.tichu_pingt_48_flag
       ,a.24hour_flag
       ,a.23hour_flag
       ,a.48hour_flag
       ,a.concat_wa_tran
       ,a.next_waytra_create_time
       ,a.processing_network_id_falg
       ,a.first_type_code
       ,a.update_by
       ,a.update_by_code
       ,a.update_by_name
       ,a.accept_zone_code
       ,a.accept_zone_name
       ,a.accept_zone_id
       ,a.accept_virt_id
       ,a.accept_virt_code
       ,a.accept_virt_name
       ,a.duty_zone_code
       ,a.duty_zone_name
       ,a.duty_zone_id
       ,a.duty_virt_id
       ,a.duty_virt_code
       ,a.duty_virt_name
       ,a.transfer_zone_code
       ,a.transfer_zone_name
       ,a.transfer_zone_id
       ,a.transfer_virt_id
       ,a.transfer_virt_code
       ,a.transfer_virt_name
       ,a.last_approval_time
       ,a.other_24hour_flag
       ,a.other_23hour_flag
       ,a.overtime_finish
       ,case when b.work_order_no is not null then '1' else '0' end as exclude_metric_item1
       ,case when c.work_order_no is not null then '1' else '0' end as exclude_metric_item2
       ,case when d.work_order_no is not null then '1' else '0' end as exclude_metric_item3
        ,effective_resp_state
       ,a.dt
  from jms_dm.dm_mng_complaint_daliy_waybill_detail_dt             a
  left join (select work_order_no,exclude_metric_item
               from jms_dwd.dwd_sqs_workorder_report_exclude_detail_dt
              where dt>=date_add(trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM'),1)    ---上传时间上月2号
			    and dt<=date_add(trunc('{{ execution_date | cst_ds }}','MM'),1)                   ---上传时间本月2号
			    and exclude_date>=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
                and exclude_date<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))        ---工单时间上月末
                and exclude_metric_item = 1
              group by work_order_no,exclude_metric_item )   b
    on a.work_order_no= b.work_order_no
  left join (select work_order_no,exclude_metric_item
               from jms_dwd.dwd_sqs_workorder_report_exclude_detail_dt
              where dt>=date_add(trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM'),1)    ---上传时间上月2号
			    and dt<=date_add(trunc('{{ execution_date | cst_ds }}','MM'),1)                   ---上传时间本月2号
			    and exclude_date>=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
                and exclude_date<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))        ---工单时间上月末
                and exclude_metric_item = 2
              group by work_order_no,exclude_metric_item )   c
    on a.work_order_no= c.work_order_no
  left join (select work_order_no,exclude_metric_item
               from jms_dwd.dwd_sqs_workorder_report_exclude_detail_dt
              where dt>=date_add(trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM'),1)    ---上传时间上月2号
			    and dt<=date_add(trunc('{{ execution_date | cst_ds }}','MM'),1)                   ---上传时间本月2号
			    and exclude_date>=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
                and exclude_date<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))        ---工单时间上月末
                and exclude_metric_item = 3
              group by work_order_no,exclude_metric_item )   d
    on a.work_order_no= d.work_order_no
 where a.dt >=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM') and a.dt <=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))
  and day('{{ execution_date | cst_ds }}')=2   ---判断是否是每月2号批次，每月3号跑上月数据
distribute by dt,abs(hash(waybill_no))%4;


----2.汇总数据处理 剔除项，只剔除分子、分母不剔除

with accept as (
    select accept_network_code
         , accept_proxy_code
         , accept_network_type
         , sum(if(t_1_falg = 1 and ((work_type = '普通工单' and transfer_network_type <> 1) or work_type = '平台工单'), 1,
                  0))                                            as order_1day_num     --工单总量 T-1
         , sum(if(t_2_falg = 1 and (work_type = '普通工单' and transfer_network_type <> 1), 1,
                  0))                                            as order_2day_num     --工单总量 T-2
         , sum(if(t_3_falg = 1 and (work_type = '普通工单' and transfer_network_type <> 1), 1,
                  0))                                            as order_3day_num     --工单总量 T-3
         , 0                                                     as order_4day_num     --工单总量 T-4
         , sum(if(t_1_falg = 1 and work_order_status = 3, 1, 0)) as close_num          --关闭量 T-1
         , sum(if(t_2_falg = 1 and work_type = '普通工单' and transfer_network_type <> 1 and response_state = 3, 1,
                  0))                                            as 1h_response_num    --1h响应量 T-1
         , sum(case
                   when t_3_falg = 1 and work_type = '普通工单' and transfer_network_type <> 1 and
                        total_duration / 3600 < 24 then 1
                   else 0 end)                                   as order_24result_num --普通工单24h解决量
         , 0                                                     as order_48result_num --普通工单48h解决量
         , 0                                                     as 2complaint_num     --工单二次投诉
         --新增
         , sum(if(t_2_falg = 1 and work_type = '普通工单' and transfer_network_type <> 1 and effective_resp_state = 1, 1,
                  0))                                            as one_eff_num        --1h有效响应量 T-1
		 , dt
    from jms_dm.dm_mng_complaint_daliy_waybill_detail_dt
    where dt >=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
     and dt<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))          ---工单时间上月末
     and day('{{ execution_date | cst_ds }}')=2   ---判断是否是每月2号批次，每月3号跑上月数据
    group by accept_network_code
           , accept_proxy_code
           , accept_network_type
		   , dt
),
     duty as (
         select duty_network_code
              , duty_proxy_code
              , duty_network_type
              , 0                   as order_1day_num     --工单总量 T-1
              , sum(if(t_2_falg = 1 and work_type = '平台工单' and processing_network_id_falg <> 1, 1,
                       0))          as order_2day_num     --工单总量 T-2
              , sum(if(t_3_falg = 1 and work_type = '平台工单' and processing_network_id_falg <> 1, 1,
                       0))          as order_3day_num     --工单总量 T-3
              , sum(if(t_4_falg = 1 and work_type = '平台工单', 1,
                       0))          as order_4day_num     --工单总量 T-4
              , 0                   as close_num          --关闭量 T-1
              , sum(
                 if(t_2_falg = 1 and work_type = '平台工单' and response_state = 3 and processing_network_id_falg <> 1, 1,
                    0))             as 1h_response_num    --1h响应量 T-1
              , sum(case
                        when t_3_falg = 1 and work_type = '平台工单' and processing_network_id_falg <> 1
                            and overtime_finish = 0
                            then 1
                        else 0 end) as order_24result_num --平台工单24h解决量
              , 0                   as order_48result_num --平台工单48h解决量
              , sum(case
                        when work_type = '平台工单' and t_4_falg = 1 and send_back_code = 1
                            and (waybill_no like 'JT%' and length(waybill_no) = 15) then 1
                        else 0 end) as 2complaint_num     --工单二次投诉
              , sum(
                 if(t_2_falg = 1 and work_type = '平台工单' and processing_network_id_falg <> 1 and
                    effective_resp_state = 1, 1,
                    0))             as one_eff_num        --1h有效响应量 T-1
			  ,dt
         from jms_dm.dm_mng_complaint_daliy_waybill_detail_dt
    where dt >=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
     and dt<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))          ---工单时间上月末
     and day('{{ execution_date | cst_ds }}')=2   ---判断是否是每月2号批次，每月3号跑上月数据
         group by duty_network_code
                , duty_proxy_code
                , duty_network_type
				, dt
     )
        ,
     transfer as (
         select transfer_network_code
              , transfer_proxy_code
              , transfer_network_type
              , 0                   as order_1day_num     --工单总量 T-1
              , 0                   as order_2day_num     --工单总量 T-2
              , 0                   as order_3day_num     --工单总量 T-3
              , sum(if(work_type = '普通工单' and put2_ts_flag = 1 and transfer_network_type <> 1 and
                       first_type_code = '21' and source_code in (3, 6, 2), 1,
                       0))          as order_4day_num     --工单总量 T-4
              , 0                   as close_num          --关闭量 T-1
              , 0                   as 1h_response_num    --1h响应量 T-1
              , 0                   as order_24result_num --工单24h解决量
              , 0                   as order_48result_num --工单48h解决量
              , sum(case
                        when work_type = '普通工单' and put2_ts_flag = 1 and transfer_network_type <> 1
                            and first_type_code = '21' and source_code in (3, 6, 2)
                            and concat_wa_tran not like '%test%'
                            and (waybill_no is not null and waybill_no <> '')
                            and next_waytra_create_time > last_close_time
                            and (unix_timestamp(next_waytra_create_time) - unix_timestamp(last_close_time)) / 3600 < 72
                            then 1
                        else 0 end) as 2complaint_num     --工单二次投诉
              , 0                   as one_eff_num        --1h有效响应量 T-1
		      ,dt
         from jms_dm.dm_mng_complaint_daliy_waybill_detail_dt
        where dt >=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')      ---工单时间上月初
          and dt<=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))          ---工单时间上月末
          and day('{{ execution_date | cst_ds }}')=2   ---判断是否是每月2号批次，每月3号跑上月数据
         group by transfer_network_code, transfer_network_name, transfer_proxy_code, transfer_proxy_name,
                  transfer_network_type,dt
     )
insert
overwrite
table
jms_dm.dm_mng_complaint_daliy_network_summary_dt
partition
(
dt
)
select network_code                                                    as network_code       --统计网点code
     , t2.name                                                         as network_name       --统计网点name
     , proxy_code                                                      as proxy_code         --统计代理区code
     , t2.agent_name                                                   as proxy_name         --统计代理区name
     , network_type                                                    as network_type       --统计网点类型1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     , dt                                                              as date_time          --登记日期
     , sum(order_1day_num)                                             as order_1day_num     --工单总量 T-1
     , sum(order_2day_num)                                             as order_2day_num     --工单总量 T-2
     , sum(order_3day_num)                                             as order_3day_num     --工单总量 T-3
     , sum(order_4day_num)                                             as order_4day_num     --工单总量 T-4
     , sum(close_num)                                                  as close_num          --关闭量
     , sum(1h_response_num)                                            as 1h_response_num    --1h响应量
     , round(nvl(sum(1h_response_num) / sum(order_2day_num), 0), 6)    as 1h_response_rate   --1h响应率
     , sum(order_24result_num)                                         as order_24result_num --24h解决量
     , round(nvl(sum(order_24result_num) / sum(order_3day_num), 0), 6) as 24result_rate      --24h解决率
     , sum(order_48result_num)                                         as order_48result_num --48h解决量
     , round(nvl(sum(order_48result_num) / sum(order_4day_num), 0), 6) as 48result_rate      --48h解决率
     , sum(2complaint_num)                                             as 2complaint_num     --工单二次投诉量
     , round(nvl(sum(2complaint_num) / sum(order_4day_num), 0), 6)     as 2complaint_rate    --工单二次投诉率
     , t2.zone_code                                                    as zone_code          --统计网点片区code
     , t2.zone_name                                                    as zone_name          --统计网点片区name
     , t2.zone_id                                                      as zone_id            --统计网点片区id
     , coalesce(t2.virt_id, t2.agent_id)                               as virt_id            --统计网点虚拟代理区id
     , coalesce(t2.virt_code, t2.agent_code)                           as virt_code          --统计网点虚拟代理区code
     , coalesce(t2.virt_name, t2.agent_name)                           as virt_name          --统计网点虚拟代理区name
     , sum(one_eff_num)                                                as one_eff_num        --1h有效响应量 T-1
     , round(nvl(sum(one_eff_num) / sum(order_2day_num), 0), 6)        as one_eff_rate       --1h有效响应率
     , dt                                                              as dt
from (
         select accept_network_code as network_code
              , accept_proxy_code   as proxy_code
              , accept_network_type as network_type
              , order_1day_num     --工单总量 T-1
              , order_2day_num     --工单总量 T-2
              , order_3day_num     --工单总量 T-3
              , order_4day_num     --工单总量 T-4
              , close_num          --关闭量 T-1
              , 1h_response_num    --1h响应量 T-1
              , order_24result_num --工单24h解决量
              , order_48result_num --工单48h解决量
              , 2complaint_num     --工单二次投诉
              , one_eff_num        --1h有效响应量 T-1
			  , dt
         from accept
         union all
         select duty_network_code as network_code
              , duty_proxy_code   as proxy_code
              , duty_network_type as network_type
              , order_1day_num     --工单总量 T-1
              , order_2day_num     --工单总量 T-2
              , order_3day_num     --工单总量 T-3
              , order_4day_num     --工单总量 T-4
              , close_num          --关闭量 T-1
              , 1h_response_num    --1h响应量 T-1
              , order_24result_num --工单24h解决量
              , order_48result_num --工单48h解决量
              , 2complaint_num     --工单二次投诉
              , one_eff_num        --1h有效响应量 T-1
			  , dt
         from duty
         union all
         select transfer_network_code as network_code
              , transfer_proxy_code   as proxy_code
              , transfer_network_type as network_type
              , order_1day_num     --工单总量 T-1
              , order_2day_num     --工单总量 T-2
              , order_3day_num     --工单总量 T-3
              , order_4day_num     --工单总量 T-4
              , close_num          --关闭量 T-1
              , 1h_response_num    --1h响应量 T-1
              , order_24result_num --工单24h解决量
              , order_48result_num --工单48h解决量
              , 2complaint_num     --工单二次投诉
              , one_eff_num        --1h有效响应量 T-1
			  , dt
         from transfer
     ) t
         left join
     (
         select code
              , name
              , zone_code
              , zone_name
              , zone_id
              , virt_id
              , virt_code
              , virt_name
              , agent_id
              , agent_code
              , agent_name
         from jms_dim.dim_network_whole_massage
     ) t2
     on t.network_code = t2.code
where network_code is not null
  and network_code <> ''
group by network_code
       , t2.name
       , proxy_code
       , t2.agent_name
       , network_type
       , t2.zone_code
       , t2.zone_name
       , t2.zone_id
       , t2.virt_id
       , t2.virt_code
       , t2.virt_name
       , t2.agent_id
       , t2.agent_code
       , t2.agent_name
	   , dt
    distribute by dt
;

----------------------------------------------------------------------------

insert overwrite table jms_dm.dm_sqs_workorder_report_exclude_detail_upt partition (dt)
select a.waybill_no
       ,a.work_order_no
       ,a.work_order_channel
       ,a.exclude_metric_item
       ,a.first_type_code
       ,a.first_type_name
       ,a.second_type_code
       ,a.second_type_name
       ,a.problem_type_id
       ,a.problem_type_name
       ,a.duty_network_code
       ,a.duty_network_name
       ,a.transfer_network_code
       ,a.transfer_network_name
       ,a.accept_network_code
       ,a.accept_network_name
       ,a.accept_proxy_code
       ,a.accept_proxy_name
       ,a.accept_virtual_proxy_code
       ,a.accept_virtual_proxy_name
       ,a.registration_time     as registration_time
       ,a.exclude_date
       ,a.is_enable
       ,a.create_network_code
       ,a.create_network_name
       ,to_date(a.create_time)  as create_time
       ,a.create_by_code
       ,a.create_by_name
       ,a.update_time
       ,a.update_by_code
       ,a.update_by_name
       ,case when b.exclude_metric_item1='1' or b.exclude_metric_item2='1' or b.exclude_metric_item3='1' then '1' else '0' end as delete_status
       ,a.dt
 from jms_dwd.dwd_sqs_workorder_report_exclude_detail_dt  a
  left join (select work_order_no,exclude_metric_item1,exclude_metric_item2,exclude_metric_item3,dt from jms_dm.dm_mng_complaint_daliy_waybill_detail_dt
              where dt >=trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM')
			    and dt <=last_day(ADD_MONTHS('{{ execution_date | cst_ds }}',-1))
              group by work_order_no,exclude_metric_item1,exclude_metric_item2,exclude_metric_item3,dt
			  ) b
    on a.work_order_no= b.work_order_no
 where a.dt>=date_add(trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM'),1)    ---上传时间上月2号
   and a.dt<=date_add(trunc('{{ execution_date | cst_ds }}','MM'),1)               ---上传时间本月2号
   and day('{{ execution_date | cst_ds }}')=2   ---判断是否是每月2号批次，每月3号跑上月数据
   and create_time>=date_add(trunc(ADD_MONTHS('{{ execution_date | cst_ds }}',-1),'MM'),1)    ---上传时间上月2号
   and create_time<=date_add(trunc('{{ execution_date | cst_ds }}','MM'),1)                   ---上传时间本月2号
distribute by dt
;

